﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;              // 
using System.Data.SqlClient;    // 连接SQL Server数据库所用的头文件

namespace CURDBase
{
    /// <summary>
    /// 练习基础的数据库连接、insert、update、select、delete
    /// </summary>
    class CURD
    {
        // 连接字符串
        private string connStr = "Server=LAPTOP-GL2UVHG0;DataBase=MISDB;Uid=sa;Pwd=5835445";

        // 连接数据库  SqlConnection
        public void ConnectionDB()
        {
            //1.创建连接对象
            SqlConnection sqlConnection = new SqlConnection(connStr);
            //2.打开连接
            sqlConnection.Open();

            if(sqlConnection.State==ConnectionState.Open)
            {
                Console.WriteLine("连接成功");
            }

            //3.关闭连接
            sqlConnection.Close();
            if(sqlConnection.State==ConnectionState.Closed)
            {
                Console.WriteLine("连接关闭");
            }

        }

        // SqlCommand
        public void Insert()
        {
            //1.创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //2.定义SQL语句
            string sql = "INSERT INTO Employee([EmployeeName], [Gender], [NowAddress], [IdCard], [Tel], [WeChat], [OtherWork], [EntryDate], [PostId], [DepartmentId])";
            sql+=" VALUES('kd', '男', '北京', '372321199911110022', '18255463022', 'thinger22', '产品经理', '2025-06-03', 12, 11)";

            //3.创建Command对象
            //SqlCommand cmd = new SqlCommand();
            //cmd.CommandText = sql;
            //cmd.Connection = conn;
            SqlCommand cmd = new SqlCommand(sql, conn);  // 要么一步到位

            //4.打开连接
            conn.Open();
            //5.执行命令
            int l = cmd.ExecuteNonQuery();   //该方法可以执行insert、update、delete，不能执行select
            //6.关闭连接
            conn.Close();
            Console.WriteLine("受影响的行数："+l);
        }

        public void Updata()
        {
            SqlConnection conn = new SqlConnection(connStr);
            string sql = "update Employee set OtherWork='设计师' where EmployeeName='米兔'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            int l = cmd.ExecuteNonQuery();
            conn.Close();
            Console.WriteLine("受影响的行数：" + l);

        }

        public void Delete()
        {
            //1.创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //2.定义SQL语句
            string sql = "delete from Employee where EmployeeName='wade' and WeChat='thinger23'";
            //3.创建Command对象
            SqlCommand cmd = new SqlCommand(sql, conn);
            //4.打开连接
            conn.Open();
            //5.执行命令
            int l = cmd.ExecuteNonQuery();
            //6.关闭连接
            conn.Close();
            Console.WriteLine("受影响的行数：" + l);
        }

        public void GetSingleResult()
        {
            SqlConnection conn = new SqlConnection(connStr);
            string sql = "select count(*) as 员工人数 from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            Object result = cmd.ExecuteScalar();
            conn.Close();
            Console.WriteLine("员工人数："+result);
        }

        // SqlDataReader
        public void GetResult1()
        {
            SqlConnection conn = new SqlConnection(connStr);
            string sql = "select EmployeeName,Gender,IdCard,Tel from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();                    // 一定要先打开连接************
            //创建读取器  从Command对象中获取
            SqlDataReader reader = cmd.ExecuteReader();
            //循环读取
            while (reader.Read())    //Read()逐行读取，如果还有下一行返回true
            {
                // 读取结果存在reader中，抽象 reader["EmployeeName"] 或 reader[0]
                Console.WriteLine($"{reader["EmployeeName"]}\t{reader[1]}\t{reader["IdCard"]}\t" +
                    $"{reader["Tel"]}");
            }
            // 关闭读取器
            reader.Close();
            conn.Close();
        }

        public void GetResult2()
        {
            SqlConnection conn = new SqlConnection(connStr);
            string sql = "select EmployeeName,Gender,IdCard,Tel from Employee";
            sql += ";select * from Post";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                Console.WriteLine($"{reader["EmployeeName"]}\t{reader[1]}\t{reader["IdCard"]}\t" +
                    $"{reader["Tel"]}");
            }
            Console.WriteLine("***************************************");
            if(reader.NextResult()) //将数据读取器推进到下一个结果 如果有下一个结果返回true
            {
                //继续循环读取下一个结果
                while(reader.Read())
                {
                    Console.WriteLine($"{reader["PostId"]}\t{reader["PostName"]}");
                }
            }
            reader.Close();
            conn.Close();

        }

        //SqlDataAdapter
        public void GetSet1()
        {
            SqlConnection conn = new SqlConnection(connStr);
            string sql = "select EmployeeName,Gender,IdCard,Tel from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();

            //创建数据适配器对象，使用SqlCommand对象构造。依据该命令创建一个适配器对象
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            // 创建数据集对象 DataSet 是个 表table 集合
            DataSet ds = new DataSet();
            // 填充数据，  填充到数据集
            da.Fill(ds);
            conn.Close();       // 到此即可关闭连接  不要忘记

            foreach(DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}\t{row["Gender"]}\t" +
                    $"{row["IdCard"]}\t{row["Tel"]}");
            }
        }

        public void GetSet2()
        {
            SqlConnection conn = new SqlConnection(connStr);
            string sql = "select EmployeeName,Gender,IdCard,Tel from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            // 往ds中填充数据，根据第一个cmd获取的第一个结果集（表）
            // 并将该表的名称设为Employee
            da.Fill(ds, "Employee");

            //封装第二个查询命令
            cmd.CommandText = "select PostId,PostName from Post";
            da.Fill(ds, "Post");    // 填充 第二个命令的结果

            // 第三次
            cmd.CommandText = "select DepartmentId,DepartmentName from Department";
            da.Fill(ds, "Department");

            conn.Close();
            
            foreach(DataRow row in ds.Tables["Employee"].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}\t{row["Gender"]}\t" +
                    $"{row["IdCard"]}\t{row["Tel"]}");
            }
            Console.WriteLine("*************************************");
            foreach(DataRow row in ds.Tables["Post"].Rows)
            {
                Console.WriteLine($"{row["PostId"]}\t{row["PostName"]}");
            }
            Console.WriteLine("*************************************");
            foreach (DataRow row in ds.Tables["Department"].Rows)
            {
                Console.WriteLine($"{row["DepartmentId"]}\t{row["DepartmentName"]}");
            }

        }
    }
}
